
global path "data"
global path_raw "data/raw"
global path_analysis "data/analysis_data"
global path_output "data/output_tables_figures"

/*----------------------------------------------------------------------------*/
// Table 1 //

use "$path_analysis/hmda_ctn_year_analysis.dta", clear
merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
keep if _merge==3
drop _merge

putexcel set "$path_output/Table_1.xlsx", modify

putexcel A3 = "%SFHA"
putexcel A4 = "Denial rate"
putexcel A5 = "%White"
putexcel A6 = "%Male"
putexcel A7 = "Income"
putexcel A8 = "Loan"

putexcel B1 = "Full Sample" E1 = "High-SFHA" H1 = "Low-SFHA" 
putexcel B2 = "mean" C2 = "median" D2 = "sd" E2 = "mean" F2 = "median" G2 = "sd" H2 = "mean" I2 = "median" J2 = "sd" 


local j=3
sum SFHA_frac, de
putexcel B`j' = `r(mean)' C`j' = `r(p50)' D`j' = `r(sd)'
sum SFHA_frac if treatment==1, de
putexcel E`j' = `r(mean)' F`j' = `r(p50)' G`j' = `r(sd)'
sum SFHA_frac if treatment==0, de
putexcel H`j' = `r(mean)' I`j' = `r(p50)' J`j' = `r(sd)'

local j=`j'+1
sum denial_rate, de
putexcel B`j' = `r(mean)' C`j' = `r(p50)' D`j' = `r(sd)'
sum denial_rate if treatment==1, de
putexcel E`j' = `r(mean)' F`j' = `r(p50)' G`j' = `r(sd)'
sum denial_rate if treatment==0, de
putexcel H`j' = `r(mean)' I`j' = `r(p50)' J`j' = `r(sd)'

local j=`j'+1
sum white, de
putexcel B`j' = `r(mean)' C`j' = `r(p50)' D`j' = `r(sd)'
sum white if treatment==1, de
putexcel E`j' = `r(mean)' F`j' = `r(p50)' G`j' = `r(sd)'
sum white if treatment==0, de
putexcel H`j' = `r(mean)' I`j' = `r(p50)' J`j' = `r(sd)'

local j=`j'+1
sum male, de
putexcel B`j' = `r(mean)' C`j' = `r(p50)' D`j' = `r(sd)'
sum male if treatment==1, de
putexcel E`j' = `r(mean)' F`j' = `r(p50)' G`j' = `r(sd)'
sum male if treatment==0, de
putexcel H`j' = `r(mean)' I`j' = `r(p50)' J`j' = `r(sd)'

local j=`j'+1
sum income, de
putexcel B`j' = `r(mean)' C`j' = `r(p50)' D`j' = `r(sd)'
sum income if treatment==1, de
putexcel E`j' = `r(mean)' F`j' = `r(p50)' G`j' = `r(sd)'
sum income if treatment==0, de
putexcel H`j' = `r(mean)' I`j' = `r(p50)' J`j' = `r(sd)'

local j=`j'+1
sum loan, de
putexcel B`j' = `r(mean)' C`j' = `r(p50)' D`j' = `r(sd)'
sum loan if treatment==1, de
putexcel E`j' = `r(mean)' F`j' = `r(p50)' G`j' = `r(sd)'
sum loan if treatment==0, de
putexcel H`j' = `r(mean)' I`j' = `r(p50)' J`j' = `r(sd)'



***************************************************************************************************************************************************

// Table 2 //

use "$path_analysis/hmda_ctn_year_analysis.dta", clear
merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
keep if _merge==3
drop _merge

gen yvar=denial_rate
replace yvar=yvar*100
replace white=white*100
replace male=male*100

gen post=0
replace post=1 if year>=2012

gen PostxTreat=post*treatment

// Column 1
reg yvar PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_2_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 2 
reg yvar PostxTreat post treatment white male income dti, vce(cluster ctn)
outreg2 using "$path_output/Table_2_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

// adding covariates:

* House Price Index
merge 1:1 ctn year using "$path_analysis/covariates_HPI_ctn.dta" 
keep if _merge!=2
drop _merge

* The proportion of home purchase loan
merge 1:1 ctn year using "$path_analysis/covariates_purchase_refin_ratio_ctn.dta"
keep if _merge!=2
drop _merge

* The change in unemploymemt rate 
merge m:1 FIPS year using "$path_analysis/covariates_unemployment_rate_county.dta"
keep if _merge!=2
drop _merge

* GDP growth
merge m:1 FIPS year using "$path_analysis/covariates_gdp_growth_county.dta"
keep if _merge!=2
drop _merge


gen state=substr(FIPS,1,2)
destring state, gen(state_num)
destring FIPS, gen(county_num)

gen PurRatioxTreat=PurRatio*treatment
gen PurRatioxPost=PurRatio*post

// Column 3
reg yvar PostxTreat post treatment white male income dti logprice gdpg urd_county, vce(cluster ctn) 
outreg2 using "$path_output/Table_2_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 4
reg yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, vce(cluster ctn) 
outreg2 using "$path_output/Table_2_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 5
reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, a(state_num) vce(cluster ctn) 
outreg2 using "$path_output/Table_2_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 6
reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, a(county_num) vce(cluster ctn)
outreg2 using "$path_output/Table_2_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

/*----------------------------------------------------------------------------*/

// Table 2, Panel B

replace yvar=log(yvar)
// Column 1
reg yvar PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_2_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 2 
reg yvar PostxTreat post treatment white male income dti, vce(cluster ctn)
outreg2 using "$path_output/Table_2_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 3
reg yvar PostxTreat post treatment white male income dti logprice gdpg urd_county, vce(cluster ctn) 
outreg2 using "$path_output/Table_2_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 4
reg yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, vce(cluster ctn) 
outreg2 using "$path_output/Table_2_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 5
reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, a(state_num) vce(cluster ctn) 
outreg2 using "$path_output/Table_2_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 6
reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, a(county_num) vce(cluster ctn)
outreg2 using "$path_output/Table_2_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

erase "$path_output/Table_2_Panel_A.txt"
erase "$path_output/Table_2_Panel_B.txt"

***************************************************************************************************************************************************

// Table 3 //

use "$path_analysis/hmda_origination_ctn_year_analysis.dta", clear
merge 1:1 ctn year using "$path_analysis/hmda_ctn_year_analysis.dta"
keep if _merge==3
drop _merge

merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
keep if _merge==3
drop _merge

gen post=0
replace post=1 if year>=2012
gen PostxTreat=post*treatment

// Columns 1-2
reg num_ori PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_3.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg amount_ori PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_3.xls", excel aster bfmt(f) bdec(3) tdec(3)

/*----------------------------------------------------------------------------*/

// Table 3 - Part 2 - ATTOM Data //

use "$path_analysis/attom_ctn_year.dta", clear

merge 1:1 ctn year using "$path_analysis/hmda_ctn_year_analysis.dta"
keep if _merge==3
drop _merge

merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
keep if _merge==3
drop _merge

gen post=0
replace post=1 if year>=2012
gen PostxTreat=post*treatment

// Columns 3-6
reg n_transaction PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_3.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg n_cash PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_3.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg n_mortgage PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_3.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg cash_ratio PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_3.xls", excel aster bfmt(f) bdec(3) tdec(3)

* Since the analysis data "attom_ctn_year.dta" is generated using pseudo data, regression results will not perfectly match with those presented in the manuscript. 

/*----------------------------------------------------------------------------*/

// Table 3 - Part 3 - Zillow Listing Monthly Data //

use "$path_analysis/zillow_ctn_year_month.dta", clear
merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
keep if _merge==3
drop _merge

merge m:1 ctn year using "$path_analysis/hmda_ctn_year_analysis.dta"
keep if _merge==3
drop _merge
		
gen post=0
replace post=1 if year*100+month>=201207
gen PostxTreat=post*treatment

// Columns 7-8
reg dom_sold PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_3.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg ptl_sold PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_3.xls", excel aster bfmt(f) bdec(3) tdec(3)

* Since the analysis data "zillow_ctn_year_month.dta" is generated using pseudo data, regression results will not perfectly match with those presented in the manuscript. 

erase "$path_output/Table_3.txt"

***************************************************************************************************************************************************

// Table 4 //

use "$path_analysis/hmda_ctn_year_analysis.dta", clear
keep ctn year income
drop if year>2012
bysort ctn: egen x=mean(income)
drop income
rename x income
bysort ctn: keep if _n==1

merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
keep if _merge==3
drop _merge

egen pct20 = pctile(income), p(20)
egen pct40 = pctile(income), p(40)
egen pct60 = pctile(income), p(60)
egen pct80 = pctile(income), p(80)

gen quintile=.
replace quintile=1 if income<pct20
replace quintile=2 if income>=pct20 & income<pct40
replace quintile=3 if income>=pct40 & income<pct60
replace quintile=4 if income>=pct60 & income<pct80
replace quintile=5 if income>=pct80

keep ctn treatment FIPS quintile
save "$path_analysis/treatment_dummy_quintile.dta", replace

/*----------------------------------------------------------------------------*/ 

use "$path_analysis/hmda_ctn_year_analysis.dta", clear
merge m:1 ctn using "$path_analysis/treatment_dummy_quintile.dta"
keep if _merge==3
drop _merge

gen yvar=denial_rate
replace yvar=yvar*100
replace white=white*100
replace male=male*100

gen post=0
replace post=1 if year>=2012

gen PostxTreat=post*treatment

// Table 4, Panel A, Quintiles 1-5
forvalues i=1/5 {
	preserve
	keep if quintile==`i'
	reg yvar PostxTreat post treatment, vce(cluster ctn)
	outreg2 using "$path_output/Table_4_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)
	restore
}


// adding covariates:

* House Price Index
merge 1:1 ctn year using "$path_analysis/covariates_HPI_ctn.dta" 
keep if _merge!=2
drop _merge

* The proportion of home purchase loan
merge 1:1 ctn year using "$path_analysis/covariates_purchase_refin_ratio_ctn.dta"
keep if _merge!=2
drop _merge

* The change in unemploymemt rate 
merge m:1 FIPS year using "$path_analysis/covariates_unemployment_rate_county.dta"
keep if _merge!=2
drop _merge

* GDP growth
merge m:1 FIPS year using "$path_analysis/covariates_gdp_growth_county.dta"
keep if _merge!=2
drop _merge


gen state=substr(FIPS,1,2)
destring state, gen(state_num)
destring FIPS, gen(county_num)

gen PurRatioxTreat=PurRatio*treatment
gen PurRatioxPost=PurRatio*post

// Table 4, Panel B, Quintiles 1-5
forvalues i=1/5 {
	preserve
	keep if quintile==`i'
	reg yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, vce(cluster ctn) 
	outreg2 using "$path_output/Table_4_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)
	restore
}

// Table 4, Panel C, Quintiles 1-5
forvalues i=1/5 {
	preserve
	keep if quintile==`i'
	reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, a(county_num) vce(cluster ctn)
	outreg2 using "$path_output/Table_4_Panel_C.xls", excel aster bfmt(f) bdec(3) tdec(3)
	restore
}

erase "$path_output/Table_4_Panel_A.txt"
erase "$path_output/Table_4_Panel_B.txt"
erase "$path_output/Table_4_Panel_C.txt"

***************************************************************************************************************************************************

// Table 5 //

use "$path_analysis/hmda_application_ctn_year_analysis.dta", clear
merge 1:1 ctn year using "$path_analysis/hmda_ctn_year_analysis.dta"
drop _merge

merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
keep if _merge==3
drop _merge

gen post=0
replace post=1 if year>=2012
gen PostxTreat=post*treatment

replace NumApp=NumApp*1000 // per 1000 population
replace white=white*100 // in percent
replace male=male*100 // in percent

// Column 1
reg NumApp PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_5.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 2
reg white PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_5.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 3
reg male PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_5.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 4
reg income PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_5.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 5
reg dti PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_5.xls", excel aster bfmt(f) bdec(3) tdec(3)

erase "$path_output/Table_5.txt"

***************************************************************************************************************************************************

// Table 6 //

use "$path_raw/hmda_loanlevel_all.dta", clear //  Pseudo data "$path_raw/hmda_loanlevel_all_pseudo.dta" is provided in /data/raw/
merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
keep if _merge==3
drop _merge

// adding covariates:

* House Price Index
merge m:1 ctn year using "$path_analysis/covariates_HPI_ctn.dta" 
keep if _merge!=2
drop _merge

* The change in unemploymemt rate 
merge m:1 FIPS year using "$path_analysis/covariates_unemployment_rate_county.dta"
keep if _merge!=2
drop _merge

* GDP growth
merge m:1 FIPS year using "$path_analysis/covariates_gdp_growth_county.dta"
keep if _merge!=2
drop _merge


gen yvar=loan_reject*100

gen post=0
replace post=1 if year>=2012
gen PostxTreat=post*treatment

gen bank_id=0
bysort respondent_id agencycode: gen x=_n
replace x=0 if x>1
replace bank_id=sum(x)

// Columns 1-6
reg yvar PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_6.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreat post treatment, a(bank_id) vce(cluster ctn)
outreg2 using "$path_output/Table_6.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreat post treatment white male income dti, a(bank_id) vce(cluster ctn)
outreg2 using "$path_output/Table_6.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county, a(bank_id) vce(cluster ctn)
outreg2 using "$path_output/Table_6.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county, a(state_num bank_id) vce(cluster ctn)
outreg2 using "$path_output/Table_6.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county, a(county_num bank_id) vce(cluster ctn)
outreg2 using "$path_output/Table_6.xls", excel aster bfmt(f) bdec(3) tdec(3)

* Note that the regression results, using the pseudo loan-level data, will not match with those presented in the manuscript. 

erase "$path_output/Table_6.txt"

***************************************************************************************************************************************************

// Table 7 //

use "$path_analysis/hmda_ctn_year_analysis.dta", clear
merge 1:1 ctn year using "$path_analysis/hmda_denialreason_ctn_year_analysis.dta"
keep if _merge==3
drop _merge

merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
keep if _merge==3
drop _merge

gen post=0
replace post=1 if year>=2012
gen PostxTreat=post*treatment

forvalues i=1/9 {
	replace prob`i'=prob`i'*100 // in percent
}

/*
// Reasons for Denial //
Code 1—Debt-to-income ratio
Code 2—Employment history
Code 3—Credit history
Code 4—Collateral
Code 5—Insufficient cash (downpayment, closing costs)
Code 6—Unverifiable information
Code 7—Credit application incomplete
Code 8—Mortgage insurance denied
Code 9—Other
*/


// Column 1
reg prob4 PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_7.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 2
reg prob1 PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_7.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 3
reg prob3 PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_7.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 4
reg prob6 PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_7.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 5
reg prob2 PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_7.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Column 6
reg prob8 PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_7.xls", excel aster bfmt(f) bdec(3) tdec(3)

erase "$path_output/Table_7.txt"

***************************************************************************************************************************************************

// Table 8 //

// HMDA Data //

foreach x in improvement purchase refinance { // Corresponding to Panels A, B, and C of Table 8

	use "$path_analysis/hmda_`x'_ctn_year_analysis.dta", clear
	merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
	keep if _merge==3
	drop _merge

	gen yvar=denial_rate
	replace yvar=yvar*100
	replace white=white*100
	replace male=male*100

	gen post=0
	replace post=1 if year>=2012

	gen PostxTreat=post*treatment

	// Column 1
	reg yvar PostxTreat post treatment, vce(cluster ctn)
	outreg2 using "$path_output/Table_8_Panel_ABC.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

	// Column 2 
	reg yvar PostxTreat post treatment white male income dti, vce(cluster ctn)
	outreg2 using "$path_output/Table_8_Panel_ABC.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

	// adding covariates:

	* House Price Index
	merge 1:1 ctn year using "$path_analysis/covariates_HPI_ctn.dta" 
	keep if _merge!=2
	drop _merge

	* The proportion of home purchase loan
	merge 1:1 ctn year using "$path_analysis/covariates_purchase_refin_ratio_ctn.dta"
	keep if _merge!=2
	drop _merge

	* The change in unemploymemt rate 
	merge m:1 FIPS year using "$path_analysis/covariates_unemployment_rate_county.dta"
	keep if _merge!=2
	drop _merge

	* GDP growth
	merge m:1 FIPS year using "$path_analysis/covariates_gdp_growth_county.dta"
	keep if _merge!=2
	drop _merge


	gen state=substr(FIPS,1,2)
	destring state, gen(state_num)
	destring FIPS, gen(county_num)

	gen PurRatioxTreat=PurRatio*treatment
	gen PurRatioxPost=PurRatio*post

	// Column 3
	reg yvar PostxTreat post treatment white male income dti logprice gdpg urd_county, vce(cluster ctn) 
	outreg2 using "$path_output/Table_8_Panel_ABC.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

	// Column 4
	reg yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, vce(cluster ctn) 
	outreg2 using "$path_output/Table_8_Panel_ABC.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

	// Column 5
	reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, a(state_num) vce(cluster ctn) 
	outreg2 using "$path_output/Table_8_Panel_ABC.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')
	
	// Column 6
	reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, a(county_num) vce(cluster ctn)
	outreg2 using "$path_output/Table_8_Panel_ABC.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

}


foreach x in purchase refinance { // Corresponding to Panels D and E of Table 8

	use "$path_analysis/hmda_`x'_ctn_year_analysis.dta", clear
	merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
	keep if _merge==3
	drop _merge

	gen yvar=denial_rate
	replace yvar=yvar*100
	replace white=white*100
	replace male=male*100

	gen post=0
	replace post=1 if year>=2012

	gen PostxTreat=post*treatment
	
	replace yvar=log(yvar)

	// Column 1
	reg yvar PostxTreat post treatment, vce(cluster ctn)
	outreg2 using "$path_output/Table_8_Panel_DE.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

	// Column 2 
	reg yvar PostxTreat post treatment white male income dti, vce(cluster ctn)
	outreg2 using "$path_output/Table_8_Panel_DE.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

	// adding covariates:

	* House Price Index
	merge 1:1 ctn year using "$path_analysis/covariates_HPI_ctn.dta" 
	keep if _merge!=2
	drop _merge

	* The proportion of home purchase loan
	merge 1:1 ctn year using "$path_analysis/covariates_purchase_refin_ratio_ctn.dta"
	keep if _merge!=2
	drop _merge

	* The change in unemploymemt rate 
	merge m:1 FIPS year using "$path_analysis/covariates_unemployment_rate_county.dta"
	keep if _merge!=2
	drop _merge

	* GDP growth
	merge m:1 FIPS year using "$path_analysis/covariates_gdp_growth_county.dta"
	keep if _merge!=2
	drop _merge


	gen state=substr(FIPS,1,2)
	destring state, gen(state_num)
	destring FIPS, gen(county_num)

	gen PurRatioxTreat=PurRatio*treatment
	gen PurRatioxPost=PurRatio*post

	// Column 3
	reg yvar PostxTreat post treatment white male income dti logprice gdpg urd_county, vce(cluster ctn) 
	outreg2 using "$path_output/Table_8_Panel_DE.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

	// Column 4
	reg yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, vce(cluster ctn) 
	outreg2 using "$path_output/Table_8_Panel_DE.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

	// Column 5
	reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, a(state_num) vce(cluster ctn) 
	outreg2 using "$path_output/Table_8_Panel_DE.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

	// Column 6
	reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, a(county_num) vce(cluster ctn)
	outreg2 using "$path_output/Table_8_Panel_DE.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

}

erase "$path_output/Table_8_Panel_ABC.txt"
erase "$path_output/Table_8_Panel_DE.txt"
***************************************************************************************************************************************************

// Table 9 //

foreach x in owner nonowner { // Corresponding to Panels A and B of Table 9

	use "$path_analysis/hmda_`x'_ctn_year_analysis.dta", clear
	merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
	keep if _merge==3
	drop _merge

	gen yvar=denial_rate
	replace yvar=yvar*100
	replace white=white*100
	replace male=male*100

	gen post=0
	replace post=1 if year>=2012

	gen PostxTreat=post*treatment

	// Column 1
	reg yvar PostxTreat post treatment, vce(cluster ctn)
	outreg2 using "$path_output/Table_9_Panel_AB.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

	// Column 2 
	reg yvar PostxTreat post treatment white male income dti, vce(cluster ctn)
	outreg2 using "$path_output/Table_9_Panel_AB.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

	// adding covariates:

	* House Price Index
	merge 1:1 ctn year using "$path_analysis/covariates_HPI_ctn.dta" 
	keep if _merge!=2
	drop _merge

	* The proportion of home purchase loan
	merge 1:1 ctn year using "$path_analysis/covariates_purchase_refin_ratio_ctn.dta"
	keep if _merge!=2
	drop _merge

	* The change in unemploymemt rate 
	merge m:1 FIPS year using "$path_analysis/covariates_unemployment_rate_county.dta"
	keep if _merge!=2
	drop _merge

	* GDP growth
	merge m:1 FIPS year using "$path_analysis/covariates_gdp_growth_county.dta"
	keep if _merge!=2
	drop _merge

	gen state=substr(FIPS,1,2)
	destring state, gen(state_num)
	destring FIPS, gen(county_num)

	gen PurRatioxTreat=PurRatio*treatment
	gen PurRatioxPost=PurRatio*post

	// Column 3
	reg yvar PostxTreat post treatment white male income dti logprice gdpg urd_county, vce(cluster ctn) 
	outreg2 using "$path_output/Table_9_Panel_AB.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

	// Column 4
	reg yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, vce(cluster ctn) 
	outreg2 using "$path_output/Table_9_Panel_AB.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

	// Column 5
	reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, a(state_num) vce(cluster ctn) 
	outreg2 using "$path_output/Table_9_Panel_AB.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

	// Column 6
	reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, a(county_num) vce(cluster ctn)
	outreg2 using "$path_output/Table_9_Panel_AB.xls", excel aster bfmt(f) bdec(3) tdec(3) addtext (Panel, `x')

}

/*----------------------------------------------------------------------------*/

// Panel C

use "$path_analysis/hmda_owner_ctn_year_analysis.dta", clear
gen NOO=0
append using "$path_analysis/hmda_nonowner_ctn_year_analysis.dta"
replace NOO=1 if NOO==.

merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
keep if _merge==3
drop _merge

// adding covariates:

* House Price Index
merge m:1 ctn year using "$path_analysis/covariates_HPI_ctn.dta" 
keep if _merge!=2
drop _merge

* The proportion of home purchase loan
merge m:1 ctn year using "$path_analysis/covariates_purchase_refin_ratio_ctn.dta"
keep if _merge!=2
drop _merge

* The change in unemploymemt rate 
merge m:1 FIPS year using "$path_analysis/covariates_unemployment_rate_county.dta"
keep if _merge!=2
drop _merge

* GDP growth
merge m:1 FIPS year using "$path_analysis/covariates_gdp_growth_county.dta"
keep if _merge!=2
drop _merge

gen yvar=denial_rate
replace yvar=yvar*100
replace white=white*100
replace male=male*100

gen post=0
replace post=1 if year>=2012

gen PostxTreat=post*treatment

gen PurRatioxTreat=PurRatio*treatment
gen PurRatioxPost=PurRatio*post

gen state=substr(FIPS,1,2)
destring state, gen(state_num)
destring FIPS, gen(county_num)

gen PostxTreatxNOO=post*treatment*NOO // NOO=1 means non-owner-occupied
gen PostxNOO=post*NOO
gen TreatxNOO=treatment*NOO


capture drop cross*
local k=1
local crosslistA=""
foreach x in white male income dti {
	gen cross`k'=`x' * NOO
	local crosslistA="`crosslistA' "+"cross`k'"
	local k=`k'+1
}

local crosslistB=""
foreach x in logprice gdpg urd_county {
	gen cross`k'=`x' * NOO
	local crosslistB="`crosslistB' "+"cross`k'"
	local k=`k'+1
}

local crosslistC=""
foreach x in PurRatio PurRatioxTreat PurRatioxPost {
	gen cross`k'=`x' * NOO
	local crosslistC="`crosslistC' "+"cross`k'"
	local k=`k'+1
}

// Panel C, Columns 1-6
reg yvar PostxTreatxNOO PostxTreat PostxNOO TreatxNOO post treatment NOO, vce(cluster ctn)
outreg2 using "$path_output/Table_9_Panel_C.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg yvar PostxTreatxNOO PostxTreat PostxNOO TreatxNOO post treatment NOO white male income dti `crosslistA', vce(cluster ctn)
outreg2 using "$path_output/Table_9_Panel_C.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg yvar PostxTreatxNOO PostxTreat PostxNOO TreatxNOO post treatment NOO white male income dti logprice gdpg urd_county `crosslistA' `crosslistB', vce(cluster ctn)
outreg2 using "$path_output/Table_9_Panel_C.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg yvar PostxTreatxNOO PostxTreat PostxNOO TreatxNOO post treatment NOO white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost `crosslistA' `crosslistB' `crosslistC', vce(cluster ctn)
outreg2 using "$path_output/Table_9_Panel_C.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreatxNOO PostxTreat PostxNOO TreatxNOO post treatment NOO white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost `crosslistA' `crosslistB' `crosslistC', a(state_num#NOO) vce(cluster ctn)
outreg2 using "$path_output/Table_9_Panel_C.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreatxNOO PostxTreat PostxNOO TreatxNOO post treatment NOO white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost `crosslistA' `crosslistB' `crosslistC', a(county_num#NOO) vce(cluster ctn)
outreg2 using "$path_output/Table_9_Panel_C.xls", excel aster bfmt(f) bdec(3) tdec(3)

erase "$path_output/Table_9_Panel_AB.txt"
erase "$path_output/Table_9_Panel_C.txt"

***************************************************************************************************************************************************

// Table 10

use "$path_raw/hmda_loanlevel_all.dta", clear //  Pseudo data "$path_raw/hmda_loanlevel_all_pseudo.dta" is provided in /data/raw/

gen state_code=substr(ctn,1,2)
drop if state_code=="60" | state_code=="66" | state_code=="69" | state_code=="72" | state_code=="78"

rename respondent_id respondentid
merge m:1 ctn respondentid agencycode year using "$path_raw/branch.dta" //  Pseudo data "$path_raw/branch_pseudo.dta" is provided in /data/raw/
keep if _merge==3
drop _merge

merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
keep if _merge==3
drop _merge

gen yvar=loan_reject*100

gen bank_id=0
bysort respondentid agencycode: gen x=_n
replace x=0 if x>1
replace bank_id=sum(x)
drop x

gen post=0
replace post=1 if year>=2012

gen PostxTreat=post*treatment

// Binary measure
* has_branch indicates whether the lender has a branch in the applicant’s census tract for a given loan application
gen PostxTreatxHB=PostxTreat*has_branch
gen postxHB=post*has_branch
gen treatmentxHB=treatment*has_branch

// Continuous measure
* min_distance measures the distance (in miles) from the property to the lender’s nearest branch
gen PostxTreatxMD=PostxTreat*min_distance
gen postxMD=post*min_distance
gen treatmentxMD=treatment*min_distance

replace min_distance=100 if min_distance==9999 // 9999 means the distance is greater than 100 (as the NBER tract-to-tract distance data is capped at 99.99 miles)

keep if min_distance<25 // Sample restriction (see detail in the manuscript)

// Panel A, Columns 1-5
reghdfe yvar PostxTreatxHB PostxTreat postxHB treatmentxHB has_branch post treatment, a(bank_id) vce(cluster ctn)
outreg2 using "$path_output/Table_10_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreatxHB PostxTreat postxHB treatmentxHB has_branch post treatment white male income dti, a(bank_id) vce(cluster ctn)
outreg2 using "$path_output/Table_10_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreatxHB PostxTreat postxHB treatmentxHB has_branch post treatment white male income dti, a(bank_id loan_purpose occupancy) vce(cluster ctn)
outreg2 using "$path_output/Table_10_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreatxHB PostxTreat postxHB treatmentxHB has_branch post treatment white male income dti, a(bank_id loan_purpose occupancy state_num) vce(cluster ctn)
outreg2 using "$path_output/Table_10_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreatxHB PostxTreat postxHB treatmentxHB has_branch post treatment white male income dti, a(bank_id loan_purpose occupancy county_num) vce(cluster ctn)
outreg2 using "$path_output/Table_10_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

// Panel B, Columns 1-5
reghdfe yvar PostxTreatxMD PostxTreat postxMD treatmentxMD min_distance post treatment, a(bank_id) vce(cluster ctn)
outreg2 using "$path_output/Table_10_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreatxMD PostxTreat postxMD treatmentxMD min_distance post treatment white male income dti, a(bank_id) vce(cluster ctn)
outreg2 using "$path_output/Table_10_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreatxMD PostxTreat postxMD treatmentxMD min_distance post treatment white male income dti, a(bank_id loan_purpose occupancy) vce(cluster ctn)
outreg2 using "$path_output/Table_10_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreatxMD PostxTreat postxMD treatmentxMD min_distance post treatment white male income dti, a(bank_id loan_purpose occupancy state_num) vce(cluster ctn)
outreg2 using "$path_output/Table_10_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreatxMD PostxTreat postxMD treatmentxMD min_distance post treatment white male income dti, a(bank_id loan_purpose occupancy county_num) vce(cluster ctn)
outreg2 using "$path_output/Table_10_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

* Note that the regression results using pseudo data will not match with those presented in the manuscript. 

erase "$path_output/Table_10_Panel_A.txt"
erase "$path_output/Table_10_Panel_B.txt"


***************************************************************************************************************************************************

// Table 11 //

clear
import delimited "$path_raw/Yale_Climate_Opinion_Survey_2016_Data.csv" // Pseudo data "Yale_Climate_Opinion_Survey_2016_Data_pseudo.csv" is provided in /data/raw/

keep if geotype=="County"

keep geoid happening
rename geoid FIPS
tostring FIPS, replace
replace FIPS="0"+FIPS if length(FIPS)==4

save "$path/climate_survey.dta", replace
/*----------------------------------------------------------------------------*/		
		
use "$path_analysis/treatment_dummy.dta", clear
merge m:1 FIPS using "$path/climate_survey.dta" // This is a pseudo dataset since the original data is proprietary (see README)
keep if _merge==3
drop _merge

egen m0 = pctile(happening) if treatment==0, p(50)
egen m1 = pctile(happening) if treatment==1, p(50)

gen SCB=0
replace SCB=1 if happening>m0 & treatment==0
replace SCB=1 if happening>m1 & treatment==1

save "$path/treatment_dummy_climate_belief.dta", replace 
/*----------------------------------------------------------------------------*/ 

		
use "$path_analysis/hmda_ctn_year_analysis.dta", clear
merge m:1 ctn using "$path/treatment_dummy_climate_belief.dta"
keep if _merge==3
drop _merge

gen yvar=denial_rate
replace yvar=yvar*100
replace white=white*100
replace male=male*100

gen post=0
replace post=1 if year>=2012

gen PostxTreat=post*treatment

// adding covariates:

* House Price Index
merge 1:1 ctn year using "$path_analysis/covariates_HPI_ctn.dta" 
keep if _merge!=2
drop _merge

* The proportion of home purchase loan
merge 1:1 ctn year using "$path_analysis/covariates_purchase_refin_ratio_ctn.dta"
keep if _merge!=2
drop _merge

* The change in unemploymemt rate 
merge m:1 FIPS year using "$path_analysis/covariates_unemployment_rate_county.dta"
keep if _merge!=2
drop _merge

* GDP growth
merge m:1 FIPS year using "$path_analysis/covariates_gdp_growth_county.dta"
keep if _merge!=2
drop _merge


gen state=substr(FIPS,1,2)
destring state, gen(state_num)
destring FIPS, gen(county_num)

gen PurRatioxTreat=PurRatio*treatment
gen PurRatioxPost=PurRatio*post

// Panel A, Columns 1-6
preserve 

keep if SCB==0 // Weak Perception

reg yvar PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_11_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg yvar PostxTreat post treatment white male income dti, vce(cluster ctn)
outreg2 using "$path_output/Table_11_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg yvar PostxTreat post treatment white male income dti logprice gdpg urd_county, vce(cluster ctn) 
outreg2 using "$path_output/Table_11_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, vce(cluster ctn) 
outreg2 using "$path_output/Table_11_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, a(state_num) vce(cluster ctn) 
outreg2 using "$path_output/Table_11_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, a(county_num) vce(cluster ctn)
outreg2 using "$path_output/Table_11_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)

restore
* Note that the regression results using pseudo data will not match with those presented in the manuscript. 

// Panel B, Columns 1-6
preserve 

keep if SCB==1 // Strong Perception

reg yvar PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_11_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg yvar PostxTreat post treatment white male income dti, vce(cluster ctn)
outreg2 using "$path_output/Table_11_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg yvar PostxTreat post treatment white male income dti logprice gdpg urd_county, vce(cluster ctn) 
outreg2 using "$path_output/Table_11_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, vce(cluster ctn) 
outreg2 using "$path_output/Table_11_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, a(state_num) vce(cluster ctn) 
outreg2 using "$path_output/Table_11_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreat post treatment white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost, a(county_num) vce(cluster ctn)
outreg2 using "$path_output/Table_11_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)

restore
* Note that the regression results using pseudo data will not match with those presented in the manuscript. 

/*----------------------------------------------------------------------------*/

gen PostxTreatxSCB=post*treatment*SCB // SCB=1 means high belief
gen PostxSCB=post*SCB
gen TreatxSCB=treatment*SCB

capture drop cross*
local k=1
local crosslistA=""
foreach x in white male income dti {
	gen cross`k'=`x' * SCB
	local crosslistA="`crosslistA' "+"cross`k'"
	local k=`k'+1
}

local crosslistB=""
foreach x in logprice gdpg urd_county {
	gen cross`k'=`x' * SCB
	local crosslistB="`crosslistB' "+"cross`k'"
	local k=`k'+1
}

local crosslistC=""
foreach x in PurRatio PurRatioxTreat PurRatioxPost {
	gen cross`k'=`x' * SCB
	local crosslistC="`crosslistC' "+"cross`k'"
	local k=`k'+1
}

// Panel C, Columns 1-6

reg yvar PostxTreatxSCB PostxTreat PostxSCB TreatxSCB post treatment SCB, vce(cluster ctn)
outreg2 using "$path_output/Table_11_Panel_C.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg yvar PostxTreatxSCB PostxTreat PostxSCB TreatxSCB post treatment SCB white male income dti `crosslistA', vce(cluster ctn)
outreg2 using "$path_output/Table_11_Panel_C.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg yvar PostxTreatxSCB PostxTreat PostxSCB TreatxSCB post treatment SCB white male income dti logprice gdpg urd_county `crosslistA' `crosslistB', vce(cluster ctn)
outreg2 using "$path_output/Table_11_Panel_C.xls", excel aster bfmt(f) bdec(3) tdec(3)

reg yvar PostxTreatxSCB PostxTreat PostxSCB TreatxSCB post treatment SCB white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost `crosslistA' `crosslistB' `crosslistC', vce(cluster ctn)
outreg2 using "$path_output/Table_11_Panel_C.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreatxSCB PostxTreat PostxSCB TreatxSCB post treatment SCB white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost `crosslistA' `crosslistB' `crosslistC', a(state_num#SCB) vce(cluster ctn)
outreg2 using "$path_output/Table_11_Panel_C.xls", excel aster bfmt(f) bdec(3) tdec(3)

reghdfe yvar PostxTreatxSCB PostxTreat PostxSCB TreatxSCB post treatment SCB white male income dti logprice gdpg urd_county PurRatio PurRatioxTreat PurRatioxPost `crosslistA' `crosslistB' `crosslistC', a(county_num#SCB) vce(cluster ctn)
outreg2 using "$path_output/Table_11_Panel_C.xls", excel aster bfmt(f) bdec(3) tdec(3)

* Note that the regression results using pseudo data will not match with those presented in the manuscript. 

erase "$path/treatment_dummy_climate_belief.dta"
erase "$path/climate_survey.dta"

erase "$path_output/Table_11_Panel_A.txt"
erase "$path_output/Table_11_Panel_B.txt"
erase "$path_output/Table_11_Panel_C.txt"


***************************************************************************************************************************************************


// Table 12 //

use "$path_analysis/ctn_construction_year_analysis.dta", clear

gen bin_abs=.
replace bin_abs=1 if construction_year>=1900 & construction_year<1955
replace bin_abs=2 if construction_year>=1955 & construction_year<1960
replace bin_abs=3 if construction_year>=1960 & construction_year<1965
replace bin_abs=4 if construction_year>=1965 & construction_year<1970
replace bin_abs=5 if construction_year>=1970 & construction_year<1975
replace bin_abs=6 if construction_year>=1975 & construction_year<1980
replace bin_abs=7 if construction_year>=1980 & construction_year<1985
replace bin_abs=8 if construction_year>=1985 & construction_year<1990
replace bin_abs=9 if construction_year>=1990 & construction_year<1995
replace bin_abs=10 if construction_year>=1995 & construction_year<2000
replace bin_abs=11 if construction_year>=2000 & construction_year<2005

merge 1:1 ctn using "$path_analysis/FIRM_year_analysis.dta" // data from FEMA's Community Status Information (see detail in the manuscript)
drop _merge

replace FIRM_year=1975 if FIRM_year<1975
* recall FEMA's definition of post-FIRM: the start of construction was after December 31, 1974, or after the initial FIRM for the community, whichever is later

gen rel_year=construction_year-FIRM_year

gen bin_rel=.
replace bin_rel=1 if rel_year>=-50 & rel_year<-25
replace bin_rel=2 if rel_year>=-25 & rel_year<-20
replace bin_rel=3 if rel_year>=-20 & rel_year<-15
replace bin_rel=4 if rel_year>=-15 & rel_year<-10
replace bin_rel=5 if rel_year>=-10 & rel_year<-5
replace bin_rel=6 if rel_year>=-5 & rel_year<0
replace bin_rel=7 if rel_year>=0 & rel_year<5
replace bin_rel=8 if rel_year>=5 & rel_year<10
replace bin_rel=9 if rel_year>=10 & rel_year<15
replace bin_rel=10 if rel_year>=15 & rel_year<20
replace bin_rel=11 if rel_year>=20 & rel_year!=.

save "$path_analysis/FIRM_bins.dta", replace 

/*----------------------------------------------------------------------------*/

use "$path_analysis/hmda_ctn_year_analysis.dta", clear
merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
keep if _merge==3
drop _merge
merge m:1 ctn using "$path_analysis/FIRM_bins.dta"
keep if _merge==3

gen yvar=denial_rate
replace yvar=yvar*100
replace white=white*100
replace male=male*100

gen post=0
replace post=1 if year>=2012

gen PostxTreat=post*treatment

// Panel A, Columns 1-11
forvalues i=1/11 {
preserve
keep if bin_abs==`i'
reg yvar PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_12_Panel_A.xls", excel aster bfmt(f) bdec(3) tdec(3)
restore
}

// Panel B, Columns 1-11
forvalues i=1/11 {
preserve
keep if bin_rel==`i'
reg yvar PostxTreat post treatment, vce(cluster ctn)
outreg2 using "$path_output/Table_12_Panel_B.xls", excel aster bfmt(f) bdec(3) tdec(3)
restore
}

erase "$path_analysis/FIRM_bins.dta"

erase "$path_output/Table_12_Panel_A.txt"
erase "$path_output/Table_12_Panel_B.txt"

***************************************************************************************************************************************************

// Figure 1 //

// Panel (a) //

use "$path_analysis/policies_ctn_monthly_analysis.dta", clear
merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
keep if _merge==3
drop _merge

drop if time>ym(2018,1)
drop if time<ym(2011,1)

gen yvar=policy

bysort treatment time: egen avg=mean(yvar)
bysort treatment time: keep if _n==1

sum time
local xstart=r(min)
local xend=r(max)
twoway (line avg time if treatment==0, lpattern(dash) yaxis(1) ylabel(26 (5) 46, axis(1) labsize(small)) ytitle("Number of Policies In-force in Low-SFHA Tracts"  " ", axis(1) size(small))) ///
(line avg time if treatment==1, yaxis(2) ytitle(" " "Number of Policies In-force in High-SFHA Tracts", axis(2) size(small)) ylabel(110 (5) 130, axis(2) labsize(small))), ///
xlabel(`xstart' (12) `xend', labsize(small)) xtitle("") ///
legend(size(small) label(1 "Control: Low-SFHA Tracts") label(2 "Treated: High-SFHA Tracts") rows(1) symysize(3) symxsize(6)) ///
graphregion(color(white)) plotregion(color(white)) 

graph export "$path_output/Figure_1_Panel_A.pdf", replace 

/*----------------------------------------------------------------------------*/


// census tract level - average premium for all policies in-force


// Figure 1, Panel (b) //

use "$path_analysis/premium_SFHA_ctn_monthly_analysis.dta", clear
merge 1:1 ctn time using "$path_analysis/premium_nonSFHA_ctn_monthly_analysis.dta"
keep if _merge==3
drop _merge

gen tot=policy_SFHA+policy_nonSFHA
gen premium=premium_avg_SFHA*policy_SFHA/tot + premium_avg_nonSFHA*policy_nonSFHA/tot

merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
keep if _merge==3
drop _merge

drop if time>ym(2018,1) | time<ym(2011,1)

bysort treatment time: egen avg=mean(premium)
bysort treatment time: keep if _n==1

sum time
local xstart=r(min)
local xend=r(max)
twoway (line avg time if treatment==0, lpattern(dash) yaxis(1) ylabel(495 (50) 745, axis(1) labsize(small)) yscale(r(487 737)) ytitle("Average Premium ($) for Low-SFHA Tracts"  " ", axis(1) size(small))) ///
(line avg time if treatment==1, yaxis(2) ytitle(" " "Average Premium ($) for High-SFHA Tracts", axis(2) size(small)) ylabel(750 (50) 1000, axis(2) labsize(small))), ///
xlabel(`xstart' (12) `xend', labsize(small)) xtitle("") ///
legend(size(small) label(1 "Control: Low-SFHA Tracts") label(2 "Treated: High-SFHA Tracts") rows(1) symysize(3) symxsize(6)) ///
graphregion(color(white)) plotregion(color(white)) 

graph export "$path_output/Figure_1_Panel_B.pdf", replace 

***************************************************************************************************************************************************


// Figure 2 //

use "$path_analysis/hmda_ctn_year_analysis.dta", clear
merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
keep if _merge==3
drop _merge

gen yvar=denial_rate
replace yvar=yvar*100

bysort treatment year: egen avg=mean(yvar)
bysort treatment year: keep if _n==1

twoway (line avg year if treatment==0, lpattern(dash) yaxis(1) ylabel(21 (2) 31, axis(1) labsize(small)) ytitle("Mortgage Denial Rate (in percent) for Low-SFHA Tracts"  " ", axis(1) size(small))) ///
(line avg year if treatment==1, yaxis(2) ytitle(" " "Mortgage Denial Rate (in percent) for High-SFHA Tracts", axis(2) size(small)) ylabel(22 (2) 32, axis(2) labsize(small))), ///
xlabel(2007 (1) 2016, labsize(small)) xtitle("") ///
legend(size(small) label(1 "Control: Low-SFHA Tracts") label(2 "Treated: High-SFHA Tracts") rows(1) symysize(3) symxsize(6)) ///
graphregion(color(white)) plotregion(color(white)) ///
xline(2012, lpattern(dash) lcolor(grey) extend)

graph export "$path_output/Figure_2.pdf", replace 

***************************************************************************************************************************************************

// Figure 3 //

use "$path_analysis/hmda_ctn_year_analysis.dta", clear
merge m:1 ctn using "$path_analysis/treatment_dummy.dta"
keep if _merge==3
drop _merge

gen event_time=year-2012

global L=4 // Number of pre-treatment periods
global R=4 // Number of post-treatment periods (not including 2012)
global LR=$L + $R +1 // Number of total periods

keep if event_time>=-$L & event_time<=$R

gen yvar=denial_rate
replace yvar=yvar*100


forvalues i=1/$L {
	gen L`i'PT=(event_time==-`i')*treatment
	gen L`i'P=(event_time==-`i')
}

forvalues i=0/$R {
	gen R`i'PT=(event_time==`i')*treatment
	gen R`i'P=(event_time==`i')
}

global PTlist=""
forvalues i=1/$L {
	local j=$L-`i'+1
	if `j'!=1 {
	global PTlist="$PTlist" + " L`j'PT"
	}
}
forvalues i=0/$R {
	global PTlist="$PTlist" + " R`i'PT"
}

global Plist=""
forvalues i=1/$L {
	local j=$L-`i'+1
	if `j'!=1 {
	global Plist="$Plist" + " L`j'P"
	}
}
forvalues i=0/$R {
	global Plist="$Plist" + " R`i'P"
}

reg yvar $PTlist $Plist treatment, vce(cluster ctn)

* Figure 3 plots the coefficient estimates for L4PT, L3PT, L2PT, R0PT, R1PT, R2PT, R3PT, R4PT, with 1% C.I.

matrix v=e(V)
matrix b=e(b)
forvalues j=1/$LR {
	
	if `j'<=$L -1 {
		local se`j'=sqrt(v[`j',`j'])
		local coe`j'=b[1,`j']
	}
	if `j'==$L {
		local se`j'=0
		local coe`j'=0
	}
	if `j'>=$L +1 {
		local jj=`j'-1
		local se`j'=sqrt(v[`jj',`jj'])
		local coe`j'=b[1,`jj']
	}
	
}

forvalues j=1/$LR {
	dis "`coe`j'' - `se`j''"
}

forvalues j=1/$LR {
	local seh`j'=`coe`j''+2.576*`se`j''
	local sel`j'=`coe`j''-2.576*`se`j''
}

global part1=""
forvalues j=1/$LR {
	local jj=`j'-$L -1
	global part1="$part1" + " " + "(scatteri `seh`j'' `jj' `sel`j'' `jj', c(l) msym(i) color(navy) lwidth(vthin))"
}

global part2=""
forvalues j=1/$LR {
	local jj=`j'-$L -1
	global part2="$part2" + " " + "`coe`j'' `jj'"
}

global part3=""
forvalues j=1/$LR {
	local jj=`j'-$L -1
	local j0=`jj'-0.1
	local j1=`jj'+0.1
	global part3="$part3" + " " + "(scatteri `seh`j'' `j0' `seh`j'' `j1', c(l) msym(i) color(navy) lwidth(vthin)) (scatteri `sel`j'' `j0' `sel`j'' `j1', c(l) msym(i) color(navy) lwidth(vthin))"
}

global xlabel=""
forvalues j=1/$LR {
	local jj=`j'-$L -1
	local yy=2012-$L+`j'-1
	global xlabel=`" $xlabel "' + " `jj' " + `" `" "`yy'" "' "'
}

twoway $part1 /*
*/ (scatteri $part2, msym(0) color(cranberry)) /*
*/ $part3 ,/*
*/ legend(off) /*
*/ graphregion(color(white)) plotregion(color(white)) /*
*/ xline(0 , lpattern(dash) extend) yline(0, lpattern(dash) extend)/*
*/ xlabel($xlabel , labsize(small)) /*
*/ ylabel(, labsize(small)) /*
*/ ytitle("Denial Rate (in percent)" " ", size(small)) xtitle(" " "Year" , size(small)) /*
*/ title("", size(small)) ylabel(0 (0.5) 1.5, axis(1) labsize(small))

graph export "$path_output/Figure_3.pdf", replace 


***************************************************************************************************************************************************

// Figure 4 //

// Panel (a) //

use "$path_analysis/hmda_ctn_year_analysis.dta", clear
merge m:1 ctn using "$path_analysis/treatment_dummy_quintile.dta"
keep if _merge==3
drop _merge

keep if quintile==1

gen yvar=denial_rate
replace yvar=yvar*100

bysort treatment year: egen avg=mean(yvar)
bysort treatment year: keep if _n==1

twoway (line avg year if treatment==0, lpattern(dash) yaxis(1) ylabel(29 (2) 41, axis(1) labsize(small)) ytitle("Mortgage Denial Rate (in percent) for Low-SFHA Tracts"  " ", axis(1) size(small))) ///
(line avg year if treatment==1, yaxis(2) ytitle(" " "Mortgage Denial Rate (in percent) for High-SFHA Tracts", axis(2) size(small)) ylabel(28.5 (2) 40.5, axis(2) labsize(small))), ///
xlabel(2007 (1) 2016, labsize(small)) xtitle("") ///
legend(size(small) label(1 "Low-SFHA: Income Quintile 1") label(2 "High-SFHA: Income Quintile 1") rows(1) symysize(3) symxsize(6)) ///
graphregion(color(white)) plotregion(color(white)) ///
xline(2012, lpattern(dash) lcolor(grey) extend)

graph export "$path_output/Figure_4_Panel_A.pdf", replace 

/*----------------------------------------------------------------------------*/

// Panel (b) //

use "$path_analysis/hmda_ctn_year_analysis.dta", clear
merge m:1 ctn using "$path_analysis/treatment_dummy_quintile.dta"
keep if _merge==3
drop _merge

keep if quintile==2

gen yvar=denial_rate
replace yvar=yvar*100

bysort treatment year: egen avg=mean(yvar)
bysort treatment year: keep if _n==1

twoway (line avg year if treatment==0, lpattern(dash) yaxis(1) ylabel(22 (2) 34, axis(1) labsize(small)) ytitle("Mortgage Denial Rate (in percent) for Low-SFHA Tracts"  " ", axis(1) size(small))) ///
(line avg year if treatment==1, yaxis(2) ytitle(" " "Mortgage Denial Rate (in percent) for High-SFHA Tracts", axis(2) size(small)) ylabel(22.5 (2) 34.5, axis(2) labsize(small))), ///
xlabel(2007 (1) 2016, labsize(small)) xtitle("") ///
legend(size(small) label(1 "Low-SFHA: Income Quintile 2") label(2 "High-SFHA: Income Quintile 2") rows(1) symysize(3) symxsize(6)) ///
graphregion(color(white)) plotregion(color(white)) ///
xline(2012, lpattern(dash) lcolor(grey) extend)

graph export "$path_output/Figure_4_Panel_B.pdf", replace 

/*----------------------------------------------------------------------------*/


// Panel (c) //

use "$path_analysis/hmda_ctn_year_analysis.dta", clear
merge m:1 ctn using "$path_analysis/treatment_dummy_quintile.dta"
keep if _merge==3
drop _merge

keep if quintile==3

gen yvar=denial_rate
replace yvar=yvar*100

bysort treatment year: egen avg=mean(yvar)
bysort treatment year: keep if _n==1

twoway (line avg year if treatment==0, lpattern(dash) yaxis(1) ylabel(20 (2) 32, axis(1) labsize(small)) ytitle("Mortgage Denial Rate (in percent) for Low-SFHA Tracts"  " ", axis(1) size(small))) ///
(line avg year if treatment==1, yaxis(2) ytitle(" " "Mortgage Denial Rate (in percent) for High-SFHA Tracts", axis(2) size(small)) ylabel(20 (2) 32, axis(2) labsize(small))), ///
xlabel(2007 (1) 2016, labsize(small)) xtitle("") ///
legend(size(small) label(1 "Low-SFHA: Income Quintile 3") label(2 "High-SFHA: Income Quintile 3") rows(1) symysize(3) symxsize(6)) ///
graphregion(color(white)) plotregion(color(white)) ///
xline(2012, lpattern(dash) lcolor(grey) extend)

graph export "$path_output/Figure_4_Panel_C.pdf", replace 

/*----------------------------------------------------------------------------*/

// Panel (d) //

use "$path_analysis/hmda_ctn_year_analysis.dta", clear
merge m:1 ctn using "$path_analysis/treatment_dummy_quintile.dta"
keep if _merge==3
drop _merge

keep if quintile==4

gen yvar=denial_rate
replace yvar=yvar*100

bysort treatment year: egen avg=mean(yvar)
bysort treatment year: keep if _n==1


twoway (line avg year if treatment==0, lpattern(dash) yaxis(1) ylabel(18 (2) 30, axis(1) labsize(small)) ytitle("Mortgage Denial Rate (in percent) for Low-SFHA Tracts"  " ", axis(1) size(small))) ///
(line avg year if treatment==1, yaxis(2) ytitle(" " "Mortgage Denial Rate (in percent) for High-SFHA Tracts", axis(2) size(small)) ylabel(18 (2) 30, axis(2) labsize(small))), ///
xlabel(2007 (1) 2016, labsize(small)) xtitle("") ///
legend(size(small) label(1 "Low-SFHA: Income Quintile 4") label(2 "High-SFHA: Income Quintile 4") rows(1) symysize(3) symxsize(6)) ///
graphregion(color(white)) plotregion(color(white)) ///
xline(2012, lpattern(dash) lcolor(grey) extend)

graph export "$path_output/Figure_4_Panel_D.pdf", replace 

/*----------------------------------------------------------------------------*/


// Panel (e) //

use "$path_analysis/hmda_ctn_year_analysis.dta", clear
merge m:1 ctn using "$path_analysis/treatment_dummy_quintile.dta"
keep if _merge==3
drop _merge

keep if quintile==5

gen yvar=denial_rate
replace yvar=yvar*100

bysort treatment year: egen avg=mean(yvar)
bysort treatment year: keep if _n==1

twoway (line avg year if treatment==0, lpattern(dash) yaxis(1) ylabel(16 (2) 28, axis(1) labsize(small)) ytitle("Mortgage Denial Rate (in percent) for Low-SFHA Tracts"  " ", axis(1) size(small))) ///
(line avg year if treatment==1, yaxis(2) ytitle(" " "Mortgage Denial Rate (in percent) for High-SFHA Tracts", axis(2) size(small)) ylabel(17.5 (2) 29.5, axis(2) labsize(small))), ///
xlabel(2007 (1) 2016, labsize(small)) xtitle("") ///
legend(size(small) label(1 "Low-SFHA: Income Quintile 5") label(2 "High-SFHA: Income Quintile 5") rows(1) symysize(3) symxsize(6)) ///
graphregion(color(white)) plotregion(color(white)) ///
xline(2012, lpattern(dash) lcolor(grey) extend)

graph export "$path_output/Figure_4_Panel_E.pdf", replace 



***************************************************************************************************************************************************




















